-- @owner: @zym_2025
-- @date: 2025/07/29
-- @testpoint: ddl语句使用rowcount_big()

@connect PrimaryDbDmode;

--step1:执行数据库相关的ddl，查询rowcount_big();expect:返回0
drop database if exists db_rowcount;
create database db_rowcount;
select rowcount_big();
alter database db_rowcount set datestyle to 'ISO, MDY';
select rowcount_big();
drop database db_rowcount;
select rowcount_big();

--step2:执行模式相关的ddl，查询rowcount_big();expect:返回0
drop schema if exists schema_rowcount;
create schema schema_rowcount;
select rowcount_big();
alter schema schema_rowcount rename to schema_rowcount1;
select rowcount_big();
drop schema schema_rowcount1;
select rowcount_big();

--step3:执行表空间相关的ddl，查询rowcount_big();expect:返回0
drop tablespace if exists tablespace_rowcount;
create tablespace tablespace_rowcount relative location 'row_tablespace/tablespace_1';
select rowcount_big();
alter tablespace tablespace_rowcount rename to new_tablespace_rowcount;
select rowcount_big();
drop tablespace new_tablespace_rowcount;
select rowcount_big();

--step4:执行存储过程相关的ddl，查询rowcount_big();expect:返回0
drop procedure if exists pro_rowcount;
create or replace procedure pro_rowcount() as
begin
  raise notice 'pro_rowcount';
end;
/
select rowcount_big();
drop procedure pro_rowcount;
select rowcount_big();

--step5:执行函数相关的ddl，查询rowcount_big();expect:返回0
drop function if exists func_rowcount;
create or replace function func_rowcount() returns int as
$$
declare
  i int;
begin
  i := 0;
  while i < 100 loop
    i := i + 1;
  end loop;
  return i;
end;
$$ language pltsql;
/
select rowcount_big();
--调用函数，查询rowcount_big();expect:返回1
call func_rowcount();
select rowcount_big();
--删除函数，查询rowcount_big();expect:返回0
drop function func_rowcount;
select rowcount_big();

--step6:执行触发器相关ddl，查询rowcount_big()
--step6.1:创建源表及触发表;expect:成功
drop table if exists test_trigger_src_tbl;
create table test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
drop table if exists test_trigger_des_tbl;
create table test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
--step6.2:创建触发器函数后查询rowcount_big();expect:返回0
create or replace function tri_insert_func() returns trigger as
$$
declare
begin
  insert into test_trigger_des_tbl values(NEW.id1, NEW.id2, NEW.id3);
  return NEW;
end
$$ language plpgsql;
/
select rowcount_big();
--step6.3:创建insert触发后查询rowcount_big();expect:返回0
drop trigger if exists insert_trigger on test_trigger_src_tbl;
create trigger insert_trigger before insert on test_trigger_src_tbl for each row execute procedure tri_insert_func();
/
select rowcount_big();
--step6.4:删除触发器后查询rowcount_big();expect:返回0
drop trigger insert_trigger on test_trigger_src_tbl;
select rowcount_big();

--step7:执行视图相关的ddl,查询rowcount_big();expect:返回0
drop view if exists view_rowcount;
create or replace view view_rowcount as select * from pg_tables;
select rowcount_big();
drop view view_rowcount;
select rowcount_big();

--step8:执行插件相关的ddl，查询rowcount_big();expect:返回0
drop extension if exists gms_utility;
create extension gms_utility;
select rowcount_big();
drop extension gms_utility;
select rowcount_big();

--step9:执行数据类型相关的ddl，查询rowcount_big();expect:返回0
drop type if exists type_rowcount;
create type type_rowcount as (col1 int, col2 int);
select rowcount_big();
drop type type_rowcount;
select rowcount_big();

--step10:序列相关ddl
--step10.1:创建序列，查询rowcount_big();expect:返回0
drop sequence if exists seq_rowcount;
create sequence seq_rowcount;
select rowcount_big();
--step10.2:查询序列值后使用rowcount_big();expect:返回1
select nextval('seq_rowcount');
select rowcount_big();
--step10.3:删除序列
drop sequence seq_rowcount;
select rowcount_big();

--step11:同义词相关的ddl
drop synonym if exists syn_rowcount;
create synonym syn_rowcount for pg_tables;
select rowcount_big();
drop synonym syn_rowcount;
select rowcount_big();